package com.railway.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.railway.bean.LoseCreditUser;
import com.railway.bean.Suggestions;
import com.railway.dao.SuggestionsDao;
import com.railway.dao.rowMapper.SuggestionsRowMapper;
import com.railway.util.DBUtil;

public class SuggestionsDaoImpl implements SuggestionsDao {
	
	JdbcTemplate jt = new JdbcTemplate(DBUtil.getDataSource());
	
	@Override
	public int insertSuggestion(Suggestions suggestions) {
		String sql = "insert into suggestion(user_name,user_id_type,user_id_num,user_phone,suggestion_object,suggestion_text) values(?,?,?,?,?,?)";
		return jt.update(sql,suggestions.getUserName(),suggestions.getUserIdType(),suggestions.getUserIdNum(),suggestions.getUserPhone(),suggestions.getSuggestionObject(),suggestions.getSuggestionText());
	}

	@Override
	public List<Suggestions> selectAllSuggestions(int pageNum, int pageSize) {
		String sql = "select * from suggestion limit ?,?";
		List<Suggestions> sg = jt.query(sql, new SuggestionsRowMapper(), (pageNum-1)*pageSize,pageSize);
		return sg;
	}

	@Override
	public int selectCountSuggestion() {
		String sql = "select count(1) from suggestion";
		return jt.queryForObject(sql, Integer.class);
	}

	@Override
	public int delSuggtion(int suggestionId) {
		String sql = "update suggestion set isdelete = 1 where suggestion_id = ?";
		return jt.update(sql,suggestionId);
	}

	@Override
	public int delAbsoluteSuggtion(int suggestionId) {
		String sql = "delete from suggestion where suggestion_id = ?";
		return jt.update(sql,suggestionId);
	}

	@Override
	public List<Suggestions> selectSuggestionLimit(String key, int pageNum, int pageSize) {
		String sql = "select * from suggestion where user_name = ? or user_id_num = ? limit ?,?";
		return jt.query(sql,new BeanPropertyRowMapper<Suggestions>(Suggestions.class),key,key,(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public int selectCountSuggestion(String key) {
		String sql = "select count(1) from suggestion where user_name = ? or user_id_num = ? ";
		return jt.queryForObject(sql, Integer.class,key,key);
	}

}
